
ALTER                            FUNCTION fn_ct_sls_load_backlog(

@CUSTOMER_INPUT VARCHAR(100),
@PRODUCT_INPUT VARCHAR(100),
@DATE_CATEGORY INT,
@MONTH_FROM INT,
@MONTH_TO INT,
@YEAR_INPUT INT,
@AS_OF_DATE VARCHAR(20),
@BACKLOG_TYPE INT )

RETURNS @FN_RESULTS TABLE( USR VARCHAR(50), CUSTOMER  VARCHAR(100), PRODUCT VARCHAR(100), 
MONTH INT, YEAR INT, QUANTITY INT, UNIT_PRICE FLOAT, REQ_DATE datetime, 
SCH_DATE datetime,BL_TYPE VARCHAR(30), END_USER VARCHAR(50) ) 

AS
BEGIN


	DECLARE @SALES_PERSON VARCHAR(50)
	DECLARE @END_USER VARCHAR(50)
	DECLARE @CUSTOMER VARCHAR(100)
	DECLARE @PRODUCT VARCHAR(50)
	DECLARE @MONTH INT
	DECLARE @YEAR INT
	DECLARE @QUANTITY INT
	DECLARE @OLD_QUANTITY INT
	DECLARE @OLD_PRICE FLOAT   -- Added by Gandhi on 29 July 2004 
	DECLARE @UNIT_PRICE FLOAT
	DECLARE @REQ_DATE datetime
	DECLARE @SCH_DATE datetime
	DECLARE @BL_TYPE VARCHAR(20)
	DECLARE @BACKLOGTYPE VARCHAR(30) -- Added by Gandhi on 24 July 2004 
	DECLARE @TEMP_CURSOR_INIT INT
	DECLARE @ROW_COUNT INT
	DECLARE @MKTG_PART_NUMBER VARCHAR(100) -- Added by gandhi on 21st Aug 2004
	DECLARE @SALES_ORDER_NUMBER  VARCHAR(50)-- Added by Gandhi on 24 July 2004 
	DECLARE @LINEITEM_NO INT -- Added by Gandhi on 24 July 2004 
	

	SET @TEMP_CURSOR_INIT = 1

	-- Added by Gandhi on 24th Aug 2004 for fcbl shipped report

	DECLARE @START_MONTH INT
	DECLARE @COUNT_MONTHS INT

	IF RTRIM(@MONTH_FROM) = -1
	BEGIN
		SET @START_MONTH = 0
	END
	ELSE
	BEGIN
		SET @START_MONTH = @MONTH_FROM
	END

	IF RTRIM(@MONTH_TO) = -1
	BEGIN
		SET @COUNT_MONTHS = 11
	END
	ELSE
	BEGIN
		SET @COUNT_MONTHS = @MONTH_TO
	END
	
	DECLARE @END_MONTH INT	
	SET @END_MONTH = @START_MONTH + @COUNT_MONTHS 

	DECLARE @TO_YEAR INT	
	SET @TO_YEAR = @YEAR_INPUT + @END_MONTH/12 

	DECLARE @TO_MONTH INT
	SET @TO_MONTH = @END_MONTH%12		

	-- Ends here - Gandhi

	DECLARE @YEAR_TO INT

	SET @MONTH_FROM = @MONTH_FROM + 1
	SET @MONTH_TO = @MONTH_TO + 1

	SET @YEAR_TO = @YEAR_INPUT + @MONTH_TO/12
	SET @MONTH_TO = @MONTH_TO%12

	DECLARE @PRODUCT_ID INT
	DECLARE @CUSTOMER_ID INT
	DECLARE @CATEGORY VARCHAR(30)
	
	

	IF @PRODUCT_INPUT IS NULL OR RTRIM( @PRODUCT_INPUT ) = '' OR @PRODUCT_INPUT = '0'	 
	BEGIN
		SELECT @PRODUCT_ID = '0'
	END

	ELSE
	BEGIN
		SELECT @PRODUCT_ID = PRODUCT_ID FROM PRODUCT WHERE PART_NUMBER = @PRODUCT_INPUT AND P_MARKETING_PART_NUMBER IS NOT NULL
	END

	IF @CUSTOMER_INPUT IS NULL OR RTRIM(@CUSTOMER_INPUT) = '' OR @CUSTOMER_INPUT = '0'	 
	BEGIN
		SELECT @CUSTOMER_ID = '0'
	END

	ELSE
	BEGIN
		SELECT @CUSTOMER_ID = CUSTOMER_ID FROM CUSTOMER WHERE C_NAME = @CUSTOMER_INPUT
	END

	DECLARE @TEMP_CURSOR CURSOR

	IF @CUSTOMER_ID IS NOT NULL AND @PRODUCT_ID IS NOT NULL
	BEGIN
	
		IF @DATE_CATEGORY = 0
		BEGIN
			SET @CATEGORY = 'reqDockDate'
		END
		
		ELSE IF @DATE_CATEGORY = 1
		BEGIN
			SET @CATEGORY = 'ScheduleDate'
		END
		
		-- Added by Gandhi on 24 July 2004
		IF @BACKLOG_TYPE = 0
		BEGIN
			SET @BACKLOGTYPE = 'OPEN'
		END
		
		ELSE IF @BACKLOG_TYPE = 1
		BEGIN
			SET @BACKLOGTYPE = 'HOLD'
		END
		-- End - Gandhi

		SET @TEMP_CURSOR_INIT = 0

		-- Modified by Prabhanjan (14 Jun 2004) - No need of IF condition as Product_ID and Customer_ID are checked in fn_backlog_report function.
		/*
		IF @CUSTOMER_INPUT IS NULL OR RTRIM(@CUSTOMER_INPUT) = '' OR @CUSTOMER_INPUT = '0'
		BEGIN
			SET @TEMP_CURSOR_INIT = 0

			SET @TEMP_CURSOR = CURSOR FOR
			--INSERT @FN_RESULTS 
			SELECT SALES_PERSON, CUSTOMER_NAME, PART_NUMBER, BOOKINGMONTH-1, BOOKINGYEAR, REQ_QYT[REQ_QTY], 
			UNIT_PRICE, REQ_DATE, SCHD_DATE, BACKLOGTYPE 
			FROM fn_backlog_report( @PRODUCT_ID, '0', '0', '0', @AS_OF_DATE, 'all', '0', @CATEGORY) 
			WHERE 		
			( ( BOOKINGMONTH >= @MONTH_FROM AND BOOKINGYEAR = @YEAR_INPUT ) OR
			( BOOKINGMONTH <= @MONTH_TO AND BOOKINGYEAR = @YEAR_TO AND @YEAR_TO <> @YEAR_INPUT ) OR
			( BOOKINGYEAR < @YEAR_TO AND BOOKINGYEAR > @YEAR_INPUT ) )
		END
	
		ELSE
		BEGIN
			SET @TEMP_CURSOR_INIT = 0

			SET @TEMP_CURSOR = CURSOR FOR
			--INSERT @FN_RESULTS 
			SELECT SALES_PERSON, CUSTOMER_NAME, PART_NUMBER, BOOKINGMONTH-1, BOOKINGYEAR, REQ_QYT[REQ_QTY], 
			UNIT_PRICE, REQ_DATE, SCHD_DATE, BACKLOGTYPE 
			FROM fn_backlog_report( @PRODUCT_ID, '0', @CUSTOMER_ID, '0', @AS_OF_DATE, 'all', '0', @CATEGORY) 
			WHERE 
			( ( BOOKINGMONTH >= @MONTH_FROM AND BOOKINGYEAR = @YEAR_INPUT ) OR
			( BOOKINGMONTH <= @MONTH_TO AND BOOKINGYEAR = @YEAR_TO AND @YEAR_TO <> @YEAR_INPUT) OR
			( BOOKINGYEAR < @YEAR_TO AND BOOKINGYEAR > @YEAR_INPUT ) )
		END

		*/

	IF(@BACKLOG_TYPE = 0 OR @BACKLOG_TYPE = 1)  -- This block for sales and marketing forecast entry screens 
	BEGIN

		SET @TEMP_CURSOR = CURSOR FOR
		-- Modified by Gandhi on 31 July 2004
		-- Requested Qty field replaced with Remaining Qty in the following query
		SELECT SALES_PERSON, CUSTOMER_NAME, PART_NUMBER, BOOKINGMONTH-1, BOOKINGYEAR, REM_QTY, 
		UNIT_PRICE, REQ_DATE, SCHD_DATE, BACKLOGTYPE 
		FROM fn_backlog_report( @PRODUCT_ID, '0', @CUSTOMER_ID, '0', @AS_OF_DATE, 'all', '0', @CATEGORY) 
		WHERE 
		( ( BOOKINGMONTH >= @MONTH_FROM AND BOOKINGYEAR = @YEAR_INPUT ) OR
		( BOOKINGMONTH <= @MONTH_TO AND BOOKINGYEAR = @YEAR_TO AND @YEAR_TO <> @YEAR_INPUT) OR
		( BOOKINGYEAR < @YEAR_TO AND BOOKINGYEAR > @YEAR_INPUT ) )
		-- Modification ends here - Gandhi	
		IF @TEMP_CURSOR_INIT = 0
		BEGIN
			OPEN @TEMP_CURSOR 
				FETCH NEXT FROM @TEMP_CURSOR
				INTO @SALES_PERSON, @CUSTOMER, @PRODUCT, @MONTH, @YEAR, @QUANTITY, @UNIT_PRICE, @REQ_DATE, @SCH_DATE, @BL_TYPE
				
				WHILE @@FETCH_STATUS = 0
				BEGIN
					SELECT @ROW_COUNT = COUNT(*) FROM @FN_RESULTS
					WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR
				
														
						IF @ROW_COUNT = 0 AND  @BL_TYPE = @BACKLOGTYPE
						
				                -- Modified by Gandhi on 29 July 2004 to get total unit price(revenue) of all the line items having
						-- same part number for a particular product during the same month and year  			
						BEGIN
							INSERT INTO @FN_RESULTS VALUES(@SALES_PERSON, @CUSTOMER, @PRODUCT, @MONTH, 
							@YEAR, @QUANTITY, @QUANTITY*@UNIT_PRICE, @REQ_DATE, @SCH_DATE, @BL_TYPE, NULL)
						END
		
						ELSE
						BEGIN
							SELECT @OLD_QUANTITY = QUANTITY, @OLD_PRICE = UNIT_PRICE FROM @FN_RESULTS
							WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR 
							
	
							UPDATE @FN_RESULTS SET QUANTITY = (@QUANTITY + @OLD_QUANTITY) , UNIT_PRICE = @OLD_PRICE +  ( @QUANTITY * @UNIT_PRICE )
							WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR AND @BL_TYPE = @BACKLOGTYPE
						END
						-- Modification ends here - Gandhi
						
					FETCH NEXT FROM @TEMP_CURSOR
					INTO @SALES_PERSON, @CUSTOMER, @PRODUCT, @MONTH, @YEAR, @QUANTITY, @UNIT_PRICE, @REQ_DATE, @SCH_DATE, @BL_TYPE		
				 END
			
			CLOSE @TEMP_CURSOR
			DEALLOCATE @TEMP_CURSOR
		END
		
	END
	IF( @BACKLOG_TYPE = 2 )	-- This block  added by Gandhi on 20th Aug 2004 for Forecast vs Backlog vs Shipped report
	BEGIN

		SET @TEMP_CURSOR = CURSOR FOR
		-- Modified by Gandhi on 25th Aug 2004		
		SELECT SALES_PERSON, CUSTOMER_NAME, PART_NUMBER, BOOKINGMONTH-1, BOOKINGYEAR, Req_Qyt, 
		UNIT_PRICE, REQ_DATE, SCHD_DATE, BACKLOGTYPE
		FROM fn_backlog_for_fcbl_report( @PRODUCT_ID, '0', @CUSTOMER_ID, '0', @AS_OF_DATE, 'all', '0', @CATEGORY) 
		WHERE 
		( ( BOOKINGMONTH >= @MONTH_FROM AND BOOKINGYEAR = @YEAR_INPUT AND BOOKINGMONTH-1 < @END_MONTH) OR
		( BOOKINGMONTH-1 < @TO_MONTH AND BOOKINGYEAR = @TO_YEAR AND @TO_YEAR <> @YEAR_INPUT) OR
		( BOOKINGYEAR < @TO_YEAR AND BOOKINGYEAR > @YEAR_INPUT ) )
		-- Ends here - Gandhi
		IF @TEMP_CURSOR_INIT = 0
		BEGIN
			OPEN @TEMP_CURSOR 
				FETCH NEXT FROM @TEMP_CURSOR
				INTO @SALES_PERSON, @CUSTOMER, @PRODUCT, @MONTH, @YEAR, @QUANTITY, @UNIT_PRICE, @REQ_DATE, @SCH_DATE, @BL_TYPE
			
				WHILE @@FETCH_STATUS = 0
				BEGIN
					SELECT @ROW_COUNT = COUNT(*) FROM @FN_RESULTS
					WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR

						IF @ROW_COUNT = 0 
					               	
						BEGIN
							INSERT INTO @FN_RESULTS VALUES(@SALES_PERSON, @CUSTOMER, @PRODUCT, @MONTH, 
							@YEAR, @QUANTITY, @QUANTITY * @UNIT_PRICE, @REQ_DATE, @SCH_DATE, @BL_TYPE, NULL)
						END
						ELSE
						BEGIN
							SELECT @OLD_QUANTITY = QUANTITY, @OLD_PRICE = UNIT_PRICE  FROM @FN_RESULTS
							WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR 
							
	
							UPDATE @FN_RESULTS SET QUANTITY = (@QUANTITY + @OLD_QUANTITY), UNIT_PRICE = @OLD_PRICE +  ( @QUANTITY * @UNIT_PRICE )
							WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR
						END
						-- Added by Gandhi on 21st Aug 2004 to display marketing part number						
						SELECT @MKTG_PART_NUMBER = Product.P_Marketing_part_Number from product,Process_Step  where Product.Part_Number = @PRODUCT and product.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and product.processStep_id=Process_Step.ProcessStep_ID  
						UPDATE @FN_RESULTS SET PRODUCT =  @MKTG_PART_NUMBER WHERE PRODUCT = @PRODUCT	
						-- Ends here - Gandhi
					FETCH NEXT FROM @TEMP_CURSOR
					INTO @SALES_PERSON, @CUSTOMER, @PRODUCT, @MONTH, @YEAR, @QUANTITY, @UNIT_PRICE, @REQ_DATE, @SCH_DATE, @BL_TYPE		
				END

			CLOSE @TEMP_CURSOR
			DEALLOCATE @TEMP_CURSOR
         	END
		
        END 

	IF( @BACKLOG_TYPE = 3 )	-- This block  added by Gandhi on 9th Sep 2004 for Forecast Vs. Actual report
	BEGIN

		SET @TEMP_CURSOR = CURSOR FOR
		SELECT SALES_PERSON, END_USER, CUSTOMER_NAME, PART_NUMBER, BOOKINGMONTH-1, BOOKINGYEAR, Rem_Qty, 
		UNIT_PRICE, REQ_DATE, SCHD_DATE, BACKLOGTYPE
		FROM fn_backlog_for_projected_actual_report( @PRODUCT_ID, '0', @CUSTOMER_ID, '0', @AS_OF_DATE, 'all', '0', @CATEGORY) 
		WHERE 
		( BOOKINGMONTH - 1 >= @START_MONTH AND BOOKINGYEAR = @YEAR_INPUT AND BOOKINGMONTH - 1 <= @END_MONTH ) -- Query modified by Gandhi on 14th Sep 2004
		
		IF @TEMP_CURSOR_INIT = 0
		BEGIN
			OPEN @TEMP_CURSOR 
				FETCH NEXT FROM @TEMP_CURSOR
				INTO @SALES_PERSON, @END_USER, @CUSTOMER, @PRODUCT, @MONTH, @YEAR, @QUANTITY, @UNIT_PRICE, @REQ_DATE, @SCH_DATE, @BL_TYPE
			
				WHILE @@FETCH_STATUS = 0
				BEGIN
					SELECT @ROW_COUNT = COUNT(*) FROM @FN_RESULTS
					WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR AND USR = @SALES_PERSON AND END_USER = @END_USER

						IF @ROW_COUNT = 0 
					               	
						BEGIN
							INSERT INTO @FN_RESULTS VALUES(@SALES_PERSON, @CUSTOMER, @PRODUCT, @MONTH, 
							@YEAR, @QUANTITY, @UNIT_PRICE, @REQ_DATE, @SCH_DATE, @BL_TYPE, @END_USER)
						END
						ELSE
						BEGIN
							SELECT @OLD_QUANTITY = QUANTITY  FROM @FN_RESULTS
							WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR AND USR = @SALES_PERSON AND END_USER = @END_USER 
							
	
							UPDATE @FN_RESULTS SET QUANTITY = (@QUANTITY + @OLD_QUANTITY)
							WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND MONTH = @MONTH AND YEAR = @YEAR AND USR = @SALES_PERSON AND END_USER = @END_USER
						END
						-- Added by Gandhi on 21st Aug 2004 to display marketing part number						
						SELECT @MKTG_PART_NUMBER = Product.P_Marketing_part_Number from product,Process_Step  where Product.Part_Number = @PRODUCT and product.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and product.processStep_id=Process_Step.ProcessStep_ID  
						UPDATE @FN_RESULTS SET PRODUCT =  @MKTG_PART_NUMBER WHERE PRODUCT = @PRODUCT	
						-- Ends here - Gandhi
					FETCH NEXT FROM @TEMP_CURSOR
					INTO @SALES_PERSON, @END_USER, @CUSTOMER, @PRODUCT, @MONTH, @YEAR, @QUANTITY, @UNIT_PRICE, @REQ_DATE, @SCH_DATE, @BL_TYPE		
				END

			CLOSE @TEMP_CURSOR
			DEALLOCATE @TEMP_CURSOR
         	END
		
        END 		
		
		
     END

  RETURN

END

